package com.ycl.mapper;

import com.ycl.pojo.User;
import java.sql.*;

//数据库操作层
public class UserMapper {
    //驱动
    private static String driver = "com.mysql.cj.jdbc.Driver";
    //IP地址
    private static String url = "jdbc:mysql:///y_chat?serverTimezone=GMT&useSSL=false&characterEncoding=utf-8";
    //用户名
    private static String user = "root";
    //密码
    private static String password = "123456";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    private Connection createConnection() {
        try {
            return getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 添加数据
     * @param user
     */
    public int add(User user) {
        Connection conn = createConnection();
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn.setAutoCommit(false); //开启事务
            String sql = "insert into y_chat_user (username,password,email) values (?,?,?)";
            ps = conn.prepareStatement(sql);
            //执行SQL语句
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getEmail());
            count = ps.executeUpdate();
            conn.commit(); //提交事务
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            close(null, ps, conn);
        }
        return count;
    }

    /**
     * 用户登录校验
     * @param user
     */
    public String login(User user) {
        Statement ps = null;
        Connection conn = createConnection();
        try {
            String sql = "select * from y_chat_user where username = '" + user.getUsername() + "'";
            ps = conn.createStatement();
            //执行SQL语句
            ResultSet resultSet = ps.executeQuery(sql);
            //查询到数据
            if (resultSet.next()) {
                String password = resultSet.getString("password");
                //密码正确
                if (password.equals(user.getPassword())) {
                    return "yes";
                }
                return "err_pas";
            }
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            close(null, ps, conn);
        }
        return "err_name";
    }

    /**
     * 查询用户信息
     * @param user
     */
    public String getUserInfo(User user) {
        Connection conn = createConnection();
        Statement ps = null;
        try {
            String sql = "select * from y_chat_user where username = '" + user.getUsername() + "'";
            ps = conn.createStatement();
            //执行SQL语句
            ResultSet resultSet = ps.executeQuery(sql);
            //查询到数据
            if (resultSet.next()) {
                String email = resultSet.getString("email");
                //邮箱正确
                if (email.equals(user.getEmail())) {
                    String password = resultSet.getString("password");
                    return "密码为:" + password;
                }
                return "err_email";
            }
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            close(null, ps, conn);
        }
        return "err_name";
    }

    /**
     * 注销账户
     * @param usename
     * @return
     */
    public boolean delete(String usename) {
        Connection conn = createConnection();
        PreparedStatement ps = null;
        boolean flag = false;
        int count = 0;
        try {
            conn.setAutoCommit(false); //开启事务
            String sql = "delete from y_chat_user where username=?";
            ps = conn.prepareStatement(sql);
            //执行SQL语句
            ps.setString(1, usename);

            count = ps.executeUpdate();
            if (count!=0){
                flag = true;
            }
            conn.commit(); //提交事务
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            close(null, ps, conn);
        }
        return flag;
    }

    /**
     * 修改密码
     * @param user
     * @return
     */
    public String updatePassword(User user) {
        boolean flag = false;
        Connection conn = createConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            String[] pas = user.getPassword().split("_");
            conn.setAutoCommit(false); //开启事务
            String sql1 = "select * from y_chat_user where password='"+pas[0]+"' and username='"+user.getUsername()+"'";
            System.out.println(sql1);
            ps=conn.prepareStatement(sql1);
            rs = ps.executeQuery(sql1);
            if (rs.next()){
                String sql = "update y_chat_user set password='"+pas[1]+"' where username='"+user.getUsername()+"'";
                System.out.println(sql);
                PreparedStatement ps1 = conn.prepareStatement(sql);
                count = ps1.executeUpdate(sql);
                if (count!=0){
                    conn.commit(); //提交事务
                    return "ok";
                }
            }else{
                return "error";
            }
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            close(rs, ps, conn);
        }
        return "";
    }

    /**
     * 关闭资源
     * @param rs
     * @param stmt
     * @param connection
     */
    public static void close(ResultSet rs, Statement stmt, Connection connection) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
